package com.coolgor.airvow.SQL;

/**Author: Dr.Koo
 * DATE：20070202
 * 这个DataTable可以在内存中划出一块区域，暂时存放数据库的数据。
 * 使用上与ResultSet相似。
 * 行数是从0开始，列号从1开始。
 * 变量point初始状态为-1，经过next()之后，变为0，表示指向表格中的第一条记录...
 * 变量size 存放表格的长度。
 * 程序中还有部分函数没有完成，有时间要注意检查。20070205
 */

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

public class DataTable {
	List table;
	Map row;
	String[] column;	//最原始的字段名
	String[] column1;	//供显示用的字段名，初始时是column的拷贝
	public int point;
	int size;
	
	public DataTable(){
		init();
	}
	
	public DataTable(ResultSet rs){
		init();
		this.Create(rs);
	}
	
	public void init(){
		table = new ArrayList();
		row = new HashMap();
		point = -1;
		size = 0;
	}
	//往表格中放值
	public boolean setData(){
		return true;		
	}
	
	//将rs中的数据放到表格中
	public boolean setRS(ResultSet rs){
		return this.Create(rs);
	}
	
	public boolean Create(int width,long height){
		this.column = new String[width+1];
		this.column[0]=Integer.toString(width);
		this.column1=this.column.clone();
		return true;
	}
	
	public boolean Create(ResultSet rs){
		table.clear();
		row.clear();
		table = new ArrayList();
		point = -1;
		size = 0;
		try{
			//取出所有字段名
			ResultSetMetaData rsmd = rs.getMetaData(); 
			int numCols = rsmd.getColumnCount(); 
			column = new String[numCols+1];
			column[0]=Integer.toString(numCols);
			for (int i=1;i<=numCols;i++){
				column[i]=rsmd.getColumnName(i);
			}
			//按照字段放到table中
			while (rs.next()){
				row = new HashMap();
				for (int i=1;i<=numCols;i++){
					row.put(column[i], rs.getString(i));
				}
				table.add(row);
				size++;
			} 
			this.column1=this.column.clone();
		}catch(Exception e){
			System.out.println(e.getMessage());
		} 
		return true;	
	}
	
	//打印DataTable中的数据
	public boolean print(){
		try{ 
			for (int i=1;i<=Integer.parseInt(column[0]);i++)
				System.out.print(column[i]+"    ");
			System.out.println();
			for (int i=1;i<=Integer.parseInt(column1[0]);i++)
				System.out.print(column1[i]+"    ");
			System.out.println();
			for (int i=0;i<=size;i++){
				row = (HashMap)table.get(i);
				for (int j=1;j<=Integer.parseInt(column[0]);j++){
					System.out.print(row.get(column[j])+"    ");
				}
				System.out.println();
			}
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		} 
		return true;
	}
	
	//拷贝字段名
	public void copyColumn(){
		this.column1=this.column.clone();
	}
	
	//反回DataTable中记录条数
	public int size(){
		return this.size;
	}
	
	public boolean setSize(int size){
		if (size<0) return false;
		this.size=size;
		return true;
	}
	
	//显示DataTable中的字段名
	public void desc(){
		for (int i=1;i<=Integer.parseInt(column1[0]);i++)
			System.out.println(column1[i]+"    ");
	}
	
	//释放内存空间
	public boolean clear(){
		this.table.clear();
		this.row.clear();
		this.point=-1;
		this.size=0;
		return true;
	}
	
	
	//将游标point指到第一位
	public boolean first(){
		if (this.table==null) return false;
		this.point=0;
		return true;
	}
	
	//将游标point指到最后一位
	public boolean last(){
		if (this.table==null) return false;
		this.point=this.size;
		return true;
	}
	
	//游标归零
	public boolean beforeFirst(){
		if (this.table==null) return false;
		this.point=-1;
		return true;
	}
	
	//游标指向下一位
	public boolean next(){
		if (this.size==0) return false;
		if ((this.point+1)>=this.size) return false;
		this.point=this.point+1;
		return true;
	}
	
	//获取列表字段数组
	public String[] getColumn(){
		return this.column1;
	}
	
	//获取列表宽度，即字段个数
	public int getColumnNo(){
		return Integer.parseInt(this.column1[0]);
	}
	
	//获取字段名、列名
	public String getColumnName(int columnNo){
		if (columnNo<0) return null;
		if (columnNo>this.column1.length) return null;
		return this.column1[columnNo];
	}
	
	//更新字段名
	public boolean setColumnName(int columnNo,String columnName){
		if (columnNo<0) return false;
		if (columnNo>this.column1.length) return false;
		this.column1[columnNo]=columnName;
		if (null==this.column[columnNo]) this.column[columnNo]=columnName;
		return true ;
	}
	
	//获取列表
	public List getDataList(){
		return this.table;
	}
	
	//更新列表数据
	public boolean setDataList(List table){
		if (null == table) return false;
		this.table = table;
		return true;
	}
	
	//获取当前游标所在位置记录的第column字段的值,返回值是String型。
	public String getString(int column){
	    String str = null;
		if (Integer.parseInt(this.column[0])<column) return null;
		row = (HashMap)table.get(point);
		str = (String) row.get(this.column[column]);
		return str;
	}
	
	//修改当前游标所在位置记录的第column字段的值,返回值是Boolean型。
	public boolean setString(int column,String string){
		if (Integer.parseInt(this.column[0])<column) return false;
		row = (HashMap)table.get(point);
		row.remove(this.column[column]);
		row.put(this.column[column], string);
		return true;
	}
	
	//获取当前游标所在位置记录的column字段的值,返回值是String型。
	public String getString(String column){
		String str = null;
		String col = null;
		boolean temp = false;
		for (int i=1;i<=Integer.parseInt(this.column1[0]);i++){
			if (this.column1[i].equals(column)) 
			{
				temp = true;
				col = this.column[i];
			}			
		}
		if (!temp) return null;
		row = (HashMap)table.get(point);
		str = (String) row.get(col);
		return str;
	}
	
	//获取第row行第column列的值,返回值是String型。
	public String getString(int row,int column){
		String str = null;
		if (row>table.size()) return null;
		if (Integer.parseInt(this.column[0])<column) return null;
		this.row = (HashMap)table.get(row);
		str = (String) this.row.get(this.column[column]);
		return str;
	}
	
	//获取第row行记录的column字段的值,返回值是String型。
	public String getString(int row,String column){
		String str = null;
		String col = null;
		if (row>table.size()) return null;
		boolean temp = false;
		for (int i=1;i<=Integer.parseInt(this.column1[0]);i++){
			if (this.column1[i].equals(column)) 
			{
				temp = true;
				col = this.column[i];
			}			
		}
		if (!temp) return null;
		this.row = (HashMap)table.get(row);
		str = (String) this.row.get(column);
		return str;
	}
	
	//删除当前游标所在位置的整条记录
	public boolean delRow()
	{
		this.table.remove(this.point);
		this.size--;
		return true;
	}
	
	//将数据导入到MySQL数据库
	public boolean toMySQL(String DataBaseName,String tableName) throws SQLException, InterruptedException{
		DataOperation db = DataOperationFactory.create(DataBaseName);
		if (db.hasTable(tableName)) {
			System.out.println("表"+tableName+"已经存在！");
			return false;
		}
		String sqlStr ; 
		sqlStr = "CREATE TABLE ";
		sqlStr += tableName + " (";
		for (int i=1; i<=this.getColumnNo(); i++){
			sqlStr += this.getColumnName(i)+" varchar(255), ";
		}
		sqlStr.substring(0, sqlStr.length()-2);
//		sqlStr += "PRIMARY KEY (Id)";
		sqlStr += " ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
		db.update(sqlStr);
		return true;
	}
	
	public StringBuffer toTableJSON(DataTable dt){
		StringBuffer res = new StringBuffer();
		res.append("[");
    	while (dt.next()){
    		res.append("[");
    		for (int i=1;i<=dt.getColumnNo();i++){
    			res.append("'" + com.coolgor.airvow.Util.PubFunc.leach_null(dt.getString(i)) + "',");
    		}
    		res.deleteCharAt(res.length()-1);
    		res.append("],");
    	}
    	if (res.length()>1) res.deleteCharAt(res.length()-1);
    	res.append("]");
		return res;
	}
	
	public StringBuffer toJSON(DataTable dt){
		StringBuffer res = new StringBuffer();
		res.append("[");
		while (dt.next()) {
			res.append("{");
			for (int i=1; i<=dt.getColumnNo(); i++){
				res.append("" + dt.getColumnName(i) + ":'" + com.coolgor.airvow.Util.PubFunc.leach_null(dt.getString(dt.getColumnName(i))) + "',");				
			}
			res.deleteCharAt(res.length()-1);
			res.append("},");			
		}
		res.deleteCharAt(res.length()-1);
		res.append("]");
		return res;
	}
	
}